package net.sppan.blog.utils;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.lucene.queryparser.flexible.core.util.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
  
public class ExcelKit {  
      
    public static List<String[]> read(String filename,int sheetNo,int colNum) throws InvalidFormatException, IOException {  
		InputStream inp = new FileInputStream(filename);
		Workbook workbook = WorkbookFactory.create(inp);
        Sheet sheet = workbook.getSheetAt(sheetNo);
        List<String[]> result = new ArrayList<String[]>();  
          
        int rowStart = sheet.getFirstRowNum() + 1;  
        int rowEnd = sheet.getLastRowNum();  
        for(int i = rowStart; i <= rowEnd; i++) {  
            Row row = sheet.getRow(i);
            String[] data = new String[colNum];
            if(row!=null) {
                for(int j=0;j<colNum;j++) {
                	data[j] = StringUtils.toString(row.getCell(j));
                }            	
            }
            result.add(data);
        }  
        inp.close();  
        return result;  
    }
    
    public static void write(String filename,List<String[]> rows) throws IOException {
    	String extName = filename.substring(filename.lastIndexOf("."));
    	Workbook wb = null;
    	if(extName.equalsIgnoreCase("xls")) {
            wb = new HSSFWorkbook();    		
    	}else {
    		wb = new XSSFWorkbook();
    	}
        CreationHelper createHelper = wb.getCreationHelper();
        Sheet sheet = wb.createSheet("new sheet");

        for(int i=0;i<rows.size();i++) {
        	Row row = sheet.createRow(i);
        	String[] rowData = rows.get(i);
        	for(int j=0;j<rowData.length;j++) {
                Cell cell = row.createCell(j);
                cell.setCellValue(createHelper.createRichTextString(rowData[j]));        		
        	}
        }
        // Write the output to a file
        FileOutputStream fileOut = new FileOutputStream(filename);
        wb.write(fileOut);
        fileOut.close();
    }
}